package fresher.utility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Database utility: connect database, execute sql statement
 *
 */
public class DatabaseUtility {

    //Database information
    private static final String DATABASE_NAME = "cafe";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    //Database connection, statement and result set.
    private Connection connection = null;
    private Statement statement = null;
    private ResultSet resultSet = null;

    /**
     * Database constructor, create connection and statement.
     */
    public DatabaseUtility() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/" + DATABASE_NAME, USER, PASSWORD);

            statement = connection.createStatement();
        } catch (ClassNotFoundException | SQLException exception) {
        }
    }

    /**
     * Close connection
     */
    public void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
        }
    }

    /**
     * Execute sql query statement, not that this function not suitable for
     * INSERT and DELETE statement
     *
     * @param sql sql query statement
     * @return ResultSet of query statement
     * @throws SQLException
     */
    public ResultSet query(String sql) throws SQLException {
        return statement.executeQuery(sql);
    }

    /**
     * Execute sql update statement, which include INSERT, UPDATE, or DELETE
     * statement
     *
     * @param sql sql string to execute
     * @return number of effected row
     * @throws SQLException
     */
    public int executeUpdate(String sql) throws SQLException {
        return statement.executeUpdate(sql);
    }
}
